package net.tngou.iask.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import net.tngou.iask.jdbc.PageUtil;
import net.tngou.iask.jdbc.QueryHelper;
import net.tngou.iask.pojo.Agerange;
import net.tngou.iask.pojo.Answer;
import net.tngou.iask.pojo.Cure;
import net.tngou.iask.pojo.Department;
import net.tngou.iask.pojo.Disease;
import net.tngou.iask.pojo.POJO;
import net.tngou.iask.pojo.Place;
import net.tngou.iask.pojo.Treatment;
import net.tngou.iask.util.StringUtil;

/**
 * 疾病
 * @author tngou@tngou.net
 *
 */
public class DiseaseDao extends BaseDao {

	/**
	 * 取得疾病
	 * @param page 当前页
	 * @param size 页大小
	 * @param main 实体
	 * @param keyword 关键词
	 * @return
	 */
	public PageUtil getPage(int page,int size,long main,String keyword) {
		List<Map<String, Object>> list=new ArrayList<>();
		int total=0;
		
		if(main==0)
		{
			String sql_r="SELECT * ";
			String sql_t="SELECT COUNT(id) ";
			String sql_o=" ORDER BY id DESC";//排序
			String sql=" FROM `"+POJO.DABASE+"_disease`";
			if(StringUtil.isNotEmpty(keyword))
			{
				sql=sql+" WHERE  name LIKE ? OR alias like ?";
				
				Object[] params={"%"+keyword+"%","%"+keyword+"%"};
				list=QueryHelper.query_page( sql_r+sql+sql_o, page, size, params);			
				total=(int) QueryHelper.stat(sql_t+sql, params);
			}else{
				Object[] params={};
				list=QueryHelper.query_page( sql_r+sql+sql_o, page, size, params);	
				total=(int) QueryHelper.stat(sql_t+sql, params);
			}
		}else
		{
			String sql_r ="SELECT main_disease.*,disease.name,disease.alias,disease.curerate, disease.infectivity,disease.population,disease.sex,disease.description ,survey.name as sname,survey.description as sdescription "; 
			String sql_t="SELECT COUNT(main_disease.id) ";
			String sql_o=" ORDER BY main_disease.id DESC";//排序
			String sql =" FROM `"+POJO.DABASE+"_disease` disease, `"+POJO.DABASE+"_main_disease` main_disease , `"+POJO.DABASE+"_survey` survey WHERE main_disease.disease=disease.id AND survey.id=main_disease.survey AND main_disease.main=? ";
			if(StringUtil.isNotEmpty(keyword))
			{
				sql=sql+" AND   (name LIKE ? OR alias like ?)";
				
				Object[] params={main,"%"+keyword+"%","%"+keyword+"%"};
				list=QueryHelper.query_page( sql_r+sql+sql_o, page, size, params);			
				total=(int) QueryHelper.stat(sql_t+sql, params);
			}else{
				Object[] params={main};
				list=QueryHelper.query_page( sql_r+sql+sql_o, page, size, params);	
				total=(int) QueryHelper.stat(sql_t+sql, params);
			}
		}
		return new PageUtil(list, page, size, total);
		
	}
	
	
	/**
	 * 
	 * @param page
	 * @param size
	 * @param main
	 * @param keyword
	 * @return
	 */
	public PageUtil getNotDisease(int page,int size,long main,String keyword) {
		List<Map<String, Object>> list=new ArrayList<>();
		int total=0;
		String sql_r ="SELECT * "; 
		String sql_t="SELECT COUNT(id) ";
		String sql_o=" ORDER BY id ASC";//排序
		String sql =" FROM `"+POJO.DABASE+"_disease`  WHERE  id NOT IN (SELECT disease FROM `"+POJO.DABASE+"_main_disease` WHERE main=?)";
		if(StringUtil.isNotEmpty(keyword))
		{
			sql=sql+" AND (name LIKE ? OR alias like ?)";
			
			Object[] params={main,"%"+keyword+"%","%"+keyword+"%"};
			list=QueryHelper.query_page( sql_r+sql+sql_o, page, size, params);			
			total=(int) QueryHelper.stat(sql_t+sql, params);
		}else{
			Object[] params={main};
			list=QueryHelper.query_page( sql_r+sql+sql_o, page, size, params);	
			total=(int) QueryHelper.stat(sql_t+sql, params);
		}
		return new PageUtil(list, page, size, total);
	}
	
	public PageUtil getPage(int page,int size,long main,long survey,String keyword) {
		List<Map<String, Object>> list=new ArrayList<>();
		int total=0;
		
		if(main==0)
		{
			String sql_r="SELECT * ";
			String sql_t="SELECT COUNT(id) ";
			String sql_o=" ORDER BY id DESC";//排序
			String sql=" FROM `"+POJO.DABASE+"_disease` WHERE id NOT IN (SELECT disease FROM `iask_survey_disease` WHERE survey=?)";
			if(StringUtil.isNotEmpty(keyword))
			{
				sql=sql+" AND name LIKE ? OR alias like ? ";
				
				Object[] params={survey,"%"+keyword+"%","%"+keyword+"%"};
				list=QueryHelper.query_page( sql_r+sql+sql_o, page, size, params);			
				total=(int) QueryHelper.stat(sql_t+sql, params);
			}else{
				Object[] params={survey};
				list=QueryHelper.query_page( sql_r+sql+sql_o, page, size, params);	
				total=(int) QueryHelper.stat(sql_t+sql, params);
			}
		}else
		{
			String sql_r ="SELECT main_disease.*,disease.name,disease.alias,disease.curerate, disease.infectivity,disease.population,disease.sex,disease.description ,survey.name as sname,survey.description as sdescription "; 
			String sql_t="SELECT COUNT(main_disease.id) ";
			String sql_o=" ORDER BY main_disease.id DESC";//排序
			String sql =" FROM `"+POJO.DABASE+"_disease` disease, `"+POJO.DABASE+"_main_disease` main_disease , `"+POJO.DABASE+"_survey` survey WHERE main_disease.disease=disease.id AND survey.id=main_disease.survey AND main_disease.main=? AND main_disease.disease NOT IN (SELECT disease FROM `iask_survey_disease` WHERE survey=?)";
			if(StringUtil.isNotEmpty(keyword))
			{
				sql=sql+" AND   (name LIKE ? OR alias like ?)";
				
				Object[] params={main,survey,"%"+keyword+"%","%"+keyword+"%"};
				list=QueryHelper.query_page( sql_r+sql+sql_o, page, size, params);			
				total=(int) QueryHelper.stat(sql_t+sql, params);
			}else{
				Object[] params={main,survey};
				list=QueryHelper.query_page( sql_r+sql+sql_o, page, size, params);	
				total=(int) QueryHelper.stat(sql_t+sql, params);
			}
		}
		return new PageUtil(list, page, size, total);
		
	}
	
	/**
	 * 更新疾病对应的关系表
	 * @param disease  疾病id
	 * @param place	身体部位
	 * @param agerange 年龄段
	 * @param treatment 治疗方式
	 * @param department 科室
	 */
	public void update(long disease,Long[] place,Long[] agerange,Long[] treatment,Long[] department) {
		
			
		if(place!=null)
		{
			//疾病对应实体部位
			String delete_sql="DELETE FROM `"+POJO.DABASE+"_disease_place` WHERE disease=?";
			String add_sql ="INSERT INTO `"+POJO.DABASE+"_disease_place`( `disease`, `place`) VALUES (?,?)";
			QueryHelper.update(delete_sql, disease);
			Object[][] params =new Object[place.length][2];
			for (int i = 0; i < place.length; i++) {
				params[i][0]=disease;
				params[i][1]=place[i];
			}
			QueryHelper.batch(add_sql, params );
		}
		
		//疾病对应年龄段
			
		if(agerange!=null)
		{
			String delete_sql="DELETE FROM `"+POJO.DABASE+"_disease_agerange` WHERE disease=?";
			String add_sql ="INSERT INTO `"+POJO.DABASE+"_disease_agerange`( `disease`, `agerange`) VALUES (?,?)";
			QueryHelper.update(delete_sql, disease);
			Object[][] params =new Object[agerange.length][2];
			for (int i = 0; i < agerange.length; i++) {
				params[i][0]=disease;
				params[i][1]=agerange[i];
			}
			QueryHelper.batch(add_sql, params );
		}
		
		//疾病关联治疗方法
		
		if(treatment!=null)
		{
			String delete_sql="DELETE FROM `"+POJO.DABASE+"_disease_treatment` WHERE disease=?";
			String add_sql ="INSERT INTO `"+POJO.DABASE+"_disease_treatment`( `disease`, `treatment`) VALUES (?,?)";
			QueryHelper.update(delete_sql, disease);	
			Object[][] params =new Object[treatment.length][2];
			for (int i = 0; i < treatment.length; i++) {
				params[i][0]=disease;
				params[i][1]=treatment[i];
			}
			QueryHelper.batch(add_sql, params );
		}
		
		//疾病关联科室
		
		if(department!=null)
		{
			String delete_sql="DELETE FROM `"+POJO.DABASE+"_disease_department` WHERE disease=?";
			String add_sql ="INSERT INTO `"+POJO.DABASE+"_disease_department`( `disease`, `department`) VALUES (?,?)";
			QueryHelper.update(delete_sql, disease);	
			Object[][] params =new Object[department.length][2];
			for (int i = 0; i < department.length; i++) {
				params[i][0]=disease;
				params[i][1]=department[i];
			}
			QueryHelper.batch(add_sql, params );
		}
	}
	
	
	/**
	 * 取得疾病对应的身体位置
	 * @param disease 疾病ID
	 * @return
	 */
	public List<Place> getPlace(long disease) {
		String sql="SELECT place.* FROM `"+POJO.DABASE+"_place` place,`"+POJO.DABASE+"_disease_place` disease_place WHERE place.id=disease_place.place AND disease_place.disease=?  ORDER BY id ASC";
		return QueryHelper.query(Place.class, sql, disease);	
	}
	
	/**
	 * 取得疾病对应的就诊科室
	 * @param disease  疾病ID
	 * @return
	 */
	public List<Department> getDepartment(long disease) {
		String sql="SELECT department.* FROM `"+POJO.DABASE+"_department` department,`"+POJO.DABASE+"_disease_department` disease_department WHERE department.id=disease_department.department AND disease_department.disease=?  ORDER BY id ASC";
		return QueryHelper.query(Department.class, sql, disease);	
	}
	
	/**
	 * 取得疾病对应的治疗方式
	 * @param disease  疾病ID
	 * @return
	 */
	public List<Treatment> getTreatment(long disease) {
		String sql="SELECT treatment.* FROM `"+POJO.DABASE+"_treatment` treatment,`"+POJO.DABASE+"_disease_treatment` disease_treatment WHERE treatment.id=disease_treatment.treatment AND disease_treatment.disease=?  ORDER BY id ASC";
		return QueryHelper.query(Treatment.class, sql, disease);	
	}
	
	/**
	 * 取得疾病对应的年龄段
	 * @param disease  疾病ID
	 * @return
	 */
	public List<Agerange> getAgerange(long disease) {
		String sql="SELECT agerange.* FROM `"+POJO.DABASE+"_agerange` agerange,`"+POJO.DABASE+"_disease_agerange` disease_agerange WHERE agerange.id=disease_agerange.agerange AND disease_agerange.disease=? ORDER BY id ASC";
		return QueryHelper.query(Agerange.class, sql, disease);	
	}
	
	/**
	 * 取得 身体ID
	 * @param disease
	 * @return
	 */
	public List<Integer> getPlaceId(long disease) {
		
		String sql="SELECT place FROM `"+POJO.DABASE+"_disease_place` WHERE disease=?";
		return QueryHelper.query(Integer.class, sql, disease);
	}
	/**
	 * 取得治疗方法
	 * @param disease
	 * @return
	 */
	public List<Integer> getTreatmentId(long disease) {
		
		String sql="SELECT treatment FROM `"+POJO.DABASE+"_disease_treatment` WHERE disease=?";
		return QueryHelper.query(Integer.class, sql, disease);
	}
	
	/**
	 * 取得相关科室
	 * @param disease
	 * @return
	 */
	public List<Integer> getDepartmentId(long disease) {
		
		String sql="SELECT department FROM `"+POJO.DABASE+"_disease_department` WHERE disease=?";
		return QueryHelper.query(Integer.class, sql, disease);
	}
	
	/**
	 * 取得疾病列表
	 * @param main 实体（）
	 * @return
	 */
	public List<Map<String, Object>>  getList(long main) {
		if(main==0)
		{
			String sql_r="SELECT *";
			String sql_o=" ORDER BY id DESC";//排序
			String sql=" FROM `"+POJO.DABASE+"_disease`";
			return QueryHelper.query(sql_r+sql+sql_o);
		}else
		{
			String sql_r ="SELECT main_disease.disease as id,disease.name,disease.alias,disease.curerate, disease.infectivity,disease.period,disease.population,disease.sex,disease.description ,survey.name as sname,survey.description as sdescription "; 
			String sql_o=" ORDER BY id DESC";//排序
			String sql =" FROM `"+POJO.DABASE+"_disease` disease, `"+POJO.DABASE+"_main_disease` main_disease , `"+POJO.DABASE+"_survey` survey WHERE main_disease.disease=disease.id AND survey.id=main_disease.survey AND main_disease.main=? ";
			return QueryHelper.query(sql_r+sql+sql_o,main);
		}
	}
	
	/**
	 * 取得问卷关联的疾病
	 * @param survey 文件
	 * @return
	 */
	public List<Map<String, Object>> getDisease(long survey) {
		String sql="SELECT disease.*,survey_disease.id as sdid FROM `"+POJO.DABASE+"_disease` disease,`"+POJO.DABASE+"_survey_disease` survey_disease WHERE disease.id=survey_disease.disease AND survey_disease.survey=? ORDER BY id ASC";
		return QueryHelper.query(sql, survey);	
	}
	
	
	/**
	 * 取得病人对于的疾病
	 * @param main
	 * @param patient
	 * @return
	 */
	public List<Map<String, Object>> getList(long main,long patient) {
		String sql="SELECT cure.*,disease.name as dname,survey.name as sname FROM `"+POJO.DABASE+"_cure` cure,`"+POJO.DABASE+"_disease` disease,`"+POJO.DABASE+"_survey` survey WHERE disease.id=cure.disease AND survey.id=cure.survey AND cure.main=? AND cure.patient=? ORDER BY id ASC";
		return QueryHelper.query(sql, main,patient);	
	}
	
}
